home *** CD-ROM | disk | FTP | other *** search
Text File | 1991-08-25 | 32.0 KB | 1,095 lines |
- Newsgroups: comp.sources.misc
- From: Kevin Stock <kstock@gouldfr.encore.fr>
- Subject: v22i058: oraperl - Extensions to Perl to access Oracle databases, Patch02
- Message-ID: <1991Aug25.221731.11703@sparky.IMD.Sterling.COM>
- X-Md4-Signature: 37178088c22c90a03082382d6d36b805
- Date: Sun, 25 Aug 1991 22:17:31 GMT
- Approved: kent@sparky.imd.sterling.com
-
- Submitted-by: Kevin Stock <kstock@gouldfr.encore.fr>
- Posting-number: Volume 22, Issue 58
- Archive-name: oraperl/patch02
- Environment: Perl, Oracle, Pro*C
- Patch-To: oraperl: Volume 18, Issue 10
-
- Oraperl is a set of usersubs for Perl which allow scripts to access Oracle
- databases. You need Perl v3.0.27 or later and Oracle Pro*C to build it.
- Oraperl appeared in comp.sources.misc in April 1991 (v18i010), and the first
- patch appeared there in July 1991 (v20i097).
-
- This patch does the following:
-
- Adds support for dynamically modifiable SQL statements
- Adds a Hints file explaining problems which have arisen
- Corrects an error in the quick-reference sheet
-
- To apply, please unshar this message, which will create the files Hints,
- Patch02, colons.c and mkdb.pl Then feed Patch02 into the patch program.
- Modify the Makefile as required for your installation and run make.
-
- Thanks to all those who have notified me of problems or suggested improvements,
- or even just boosted my ego by telling me that you're using it.
-
- Please let me know if you have any problems, comments or bug-fixes.
-
- ,---------------.
- ,-+-------------. | Kevin Stock
- | | E N C O R E | | kstock@gouldfr.encore.fr
- | `-------------+-'
- `---------------'
- ---------
- #!/bin/sh
- # This is a shell archive (produced by shar 3.49)
- # To extract the files from this archive, save it to a file, remove
- # everything above the "!/bin/sh" line above, and type "sh file_name".
- #
- # made 08/23/1991 07:22 UTC by kstock@mmcompta
- # Source directory /usr/local/src/cmd/oraperl
- #
- # existing files will NOT be overwritten unless -c is specified
- #
- # This shar contains:
- # length mode name
- # ------ ---------- ------------------------------------------
- # 1785 -rw-r--r-- Hints
- # 23937 -rw-r--r-- Patch02
- # 830 -rw-r--r-- colons.c
- # 1444 -rwxr-xr-x mkdb.pl
- #
- # ============= Hints ==============
- if test -f 'Hints' -a X"$1" != X"-c"; then
- echo 'x - skipping Hints (File already exists)'
- else
- echo 'x - extracting Hints (Text)'
- sed 's/^X//' << 'SHAR_EOF' > 'Hints' &&
- X
- XThis file contains hints and tips about Oraperl, dealing with problems which
- Xhave arisen in the past.
- X
- X
- XRetrieving Dates
- X================
- X
- XIf you want to retrieve a field which is declared as an Oracle DATE, then you
- Xmust explicitly format it using the SQL*Plus TO_CHAR function, for example:
- X
- X $csr = &ora_open($lda, "select to_char(sysdate, 'DD/MM/YY') from dual")
- X
- XOtherwise, Oracle tells Oraperl that the field only occupies seven bytes,
- Xand a truncation error occurs when the field is fetched. This causes
- X&ora_fetch() to return an error.
- X
- XI hope to correct this in a future patch.
- X
- X
- XBuilding on a Convex machine
- X============================
- X
- XThe strtol() function used at the start of most of the functions in orafns.c
- Xand in oracle.mus must be replaced by strtoul() to allow larger addresses to
- Xbe converted.
- X
- XThe putenv() function used in set_sid() must be replaced by setenv() .
- X
- X
- XUsing Bind Variables
- X====================
- X
- XThe support for bind variables does not reflect the full potential of Pro*C.
- X
- XFirstly, bind variables may only be numeric; named bind variables are not
- Xsupported. They must run in sequence from 1. (This is to make it easy for
- X&ora_bind() to check that it has received the correct number of parameters.)
- X
- XSecondly, they may only be used to modify values within the SQL statement,
- Xnot field or table names. Thus
- X
- X insert into telno values (:1, :2)
- X
- Xis valid, but
- X
- X select * from telno order by :1
- X
- Xis not. This made the interaction between &ora_open() and &ora_bind() simpler,
- Xbut if it's a serious restriction for you let me know, and I'll look into
- Xextending it. (Of course, there's nothing to stop you doing:
- X
- X $order_by = "name";
- X &ora_open($lda, "select * from telno order by $order_by");
- X
- Xso I don't think it should be too big a problem.)
- SHAR_EOF
- chmod 0644 Hints ||
- echo 'restore of Hints failed'
- Wc_c="`wc -c < 'Hints'`"
- test 1785 -eq "$Wc_c" ||
- echo 'Hints: original size 1785, current size' "$Wc_c"
- fi
- # ============= Patch02 ==============
- if test -f 'Patch02' -a X"$1" != X"-c"; then
- echo 'x - skipping Patch02 (File already exists)'
- else
- echo 'x - extracting Patch02 (Text)'
- sed 's/^X//' << 'SHAR_EOF' > 'Patch02' &&
- Xdiff -c /user/mis/kstock/tmp/CHANGES ./CHANGES
- X*** /user/mis/kstock/tmp/CHANGES Fri Aug 23 08:50:45 1991
- X--- ./CHANGES Fri Aug 23 09:10:27 1991
- X***************
- X*** 8,10 ****
- X--- 8,16 ----
- X Added network addresses to the manual pages
- X Added a PATCHLEVEL file
- X No functional changes
- X+
- X+ Patch 02
- X+ ========
- X+ Added support for dynamically modifiable SQL statements
- X+ Added a Hints file
- X+ Corrected an error in the quick-reference sheet
- Xdiff -c /user/mis/kstock/tmp/Makefile ./Makefile
- X*** /user/mis/kstock/tmp/Makefile Fri Aug 23 08:50:56 1991
- X--- ./Makefile Mon Aug 5 11:01:38 1991
- X***************
- X*** 3,9 ****
- X # Change these to your ORACLE installation directory and Perl source directory
- X
- X ORACLE_HOME = /usr/soft/oracle
- X! SRC = /usr/soft/public/perl_4.0.09
- X
- X # Oracle Definitions, taken from proc.mk
- X
- X--- 3,9 ----
- X # Change these to your ORACLE installation directory and Perl source directory
- X
- X ORACLE_HOME = /usr/soft/oracle
- X! SRC = /usr/soft/public/perl_4.0.10
- X
- X # Oracle Definitions, taken from proc.mk
- X
- X***************
- X*** 27,48 ****
- X DEBUG = -DPERL_DEBUGGING
- X CFLAGS = $(DEBUG) -I$(SRC) $(GLOBINCS) -O
- X
- X! oraperl: $(SRC)/uperl.o usersub.o oracle.o orafns.o getcursor.o
- X! cc -o oraperl $(SRC)/uperl.o usersub.o oracle.o orafns.o getcursor.o \
- X! -lm $(OCILIB) $(NETLIBS) $(ORALIBS) $(CLIBS) $(LIBS)
- X
- X oracle.c: $(SRC)/usub/mus oracle.mus
- X- chmod +x $(SRC)/usub/mus
- X $(SRC)/usub/mus oracle.mus >oracle.c
- X
- X! usersub.o oracle.o orafns.o getcursor.o: orafns.h
- X!
- X! print: Makefile orafns.h orafns.c oracle.mus usersub.c getcursor.c
- X! pr -fn Makefile orafns.h getcursor.c orafns.c oracle.mus usersub.c | \
- X! pr -fto4 -e > Print
- X!
- X! man: oraperl.1
- X! nroff -man oraperl.1 >oraperl.man
- X
- X clean:
- X rm -f nohup.out oraperl *.o oracle.c oraperl.man Print tags out core
- X--- 27,44 ----
- X DEBUG = -DPERL_DEBUGGING
- X CFLAGS = $(DEBUG) -I$(SRC) $(GLOBINCS) -O
- X
- X! SRCS = usersub.c oracle.c orafns.c getcursor.c colons.c
- X! OBJS = usersub.o oracle.o orafns.o getcursor.o colons.o
- X! HDRS = orafns.h
- X!
- X! oraperl: $(SRC)/uperl.o $(OBJS)
- X! cc -o oraperl $(SRC)/uperl.o $(OBJS) \
- X! -lm $(OCILIB) $(NETLIBS) $(ORALIBS) $(CLIBS) $(LIBS)
- X
- X oracle.c: $(SRC)/usub/mus oracle.mus
- X $(SRC)/usub/mus oracle.mus >oracle.c
- X
- X! $(OBJS): $(HDRS)
- X
- X clean:
- X rm -f nohup.out oraperl *.o oracle.c oraperl.man Print tags out core
- Xdiff -c /user/mis/kstock/tmp/Oracle-v5 ./Oracle-v5
- X*** /user/mis/kstock/tmp/Oracle-v5 Fri Aug 23 08:50:46 1991
- X--- ./Oracle-v5 Wed Aug 7 09:24:00 1991
- X***************
- X*** 20,28 ****
- X >
- X > LDFLAGS = -L$(ORACLE_HOME)/c/libs -L$(ORACLE_HOME)/rdbms/libs
- X >
- X! > oraperl: $(SRC)/uperl.o usersub.o oracle.o orafns.o getcursor.o
- X! > $(CC) -o oraperl $(LDFLAGS) $(SRC)/uperl.o usersub.o oracle.o orafns.o \
- X! > getcursor.o $(ORALIBS) $(LIBS)
- X
- X Second method - much simpler. Only seems to require the first two libraries
- X (libocic and liboracle) of the previous method, but this time they live under
- X--- 20,27 ----
- X >
- X > LDFLAGS = -L$(ORACLE_HOME)/c/libs -L$(ORACLE_HOME)/rdbms/libs
- X >
- X! > oraperl: $(SRC)/uperl.o $(OBJS)
- X! > $(CC) -o oraperl $(LDFLAGS) $(SRC)/uperl.o $(OBJS) $(ORALIBS) $(LIBS)
- X
- X Second method - much simpler. Only seems to require the first two libraries
- X (libocic and liboracle) of the previous method, but this time they live under
- Xdiff -c /user/mis/kstock/tmp/PATCHLEVEL ./PATCHLEVEL
- X*** /user/mis/kstock/tmp/PATCHLEVEL Fri Aug 23 08:50:47 1991
- X--- ./PATCHLEVEL Mon Aug 5 11:01:39 1991
- X***************
- X*** 1 ****
- X! 1
- X--- 1 ----
- X! 2
- Xdiff -c /user/mis/kstock/tmp/README ./README
- X*** /user/mis/kstock/tmp/README Fri Aug 23 08:50:56 1991
- X--- ./README Fri Aug 23 08:53:04 1991
- X***************
- X*** 24,30 ****
- X be changed to str_2static() with the same arguments.
- X
- X I've only tested this on an Encore Multimax 520 running UMAX V (Sys Vr3.2),
- X! using Perl 3.0.34, 4.0.00 4.0.03 and 4.0.09 with Oracle version 6, as I don't
- X have access to any other system with Pro*C. I'd appreciate any comments,
- X bug-reports etc.
- X
- X--- 24,30 ----
- X be changed to str_2static() with the same arguments.
- X
- X I've only tested this on an Encore Multimax 520 running UMAX V (Sys Vr3.2),
- X! using Perl 3.0.34, 4.0.00 4.0.03 and 4.0.10 with Oracle version 6, as I don't
- X have access to any other system with Pro*C. I'd appreciate any comments,
- X bug-reports etc.
- X
- X***************
- X*** 37,52 ****
- X getcursor.c functions to deal with the cursor pool
- X orafns.c actual functions to interact with oracle
- X usersub.c initialisation routine
- X
- X! Examples (taken from the manual page)
- X debug-p tests to see if debugging is available
- X ex.pl simple example of using the functions
- X
- X Documentation
- X oraperl.doc explains some of the thinking behind Oraperl
- X oraperl.ref quick reference (troff format)
- X oraperl.1 manual page
- X Oracle-v5 Hints for compiling Oraperl with Oracle v5
- X
- X Many thanks to Larry for Perl. Now if only we could get the Camel book
- X into France! Hmm. Any plans for "Le Livre Chameau"?
- X--- 37,59 ----
- X getcursor.c functions to deal with the cursor pool
- X orafns.c actual functions to interact with oracle
- X usersub.c initialisation routine
- X+ colons.c counts substitution variables in a statement
- X
- X! Examples
- X debug-p tests to see if debugging is available
- X ex.pl simple example of using the functions
- X+ mkdb.pl more extensive example, showing the use of ora_bind()
- X
- X Documentation
- X oraperl.doc explains some of the thinking behind Oraperl
- X oraperl.ref quick reference (troff format)
- X oraperl.1 manual page
- X+ Hints notes on using oraperl
- X Oracle-v5 Hints for compiling Oraperl with Oracle v5
- X+
- X+ Miscellaneous
- X+ CHANGES Summary of changes to Oraperl
- X+ PATCHLEVEL current patchlevel (2)
- X
- X Many thanks to Larry for Perl. Now if only we could get the Camel book
- X into France! Hmm. Any plans for "Le Livre Chameau"?
- Xdiff -c /user/mis/kstock/tmp/getcursor.c ./getcursor.c
- X*** /user/mis/kstock/tmp/getcursor.c Fri Aug 23 08:50:57 1991
- X--- ./getcursor.c Mon Aug 5 11:01:39 1991
- X***************
- X*** 269,275 ****
- X {
- X DEBUG(8, (fprintf(stderr, "check_csr(%#lx)\n", (long) csr)));
- X
- X! if (ora_findcursor(csr) && (csr->hda == NULL) && (csr->data != NULL))
- X {
- X DEBUG(8, (fputs("check_csr: valid\n", stderr)));
- X return (1);
- X--- 269,275 ----
- X {
- X DEBUG(8, (fprintf(stderr, "check_csr(%#lx)\n", (long) csr)));
- X
- X! if (ora_findcursor(csr) && (csr->hda == NULL))
- X {
- X DEBUG(8, (fputs("check_csr: valid\n", stderr)));
- X return (1);
- Xdiff -c /user/mis/kstock/tmp/oracle.mus ./oracle.mus
- X*** /user/mis/kstock/tmp/oracle.mus Fri Aug 23 08:50:57 1991
- X--- ./oracle.mus Mon Aug 5 11:01:39 1991
- X***************
- X*** 25,30 ****
- X--- 25,31 ----
- X static enum usersubs {
- X US_ora_login,
- X US_ora_open,
- X+ US_ora_bind,
- X US_ora_fetch,
- X US_ora_close,
- X US_ora_logoff,
- X***************
- X*** 53,58 ****
- X--- 54,60 ----
- X
- X make_usub("ora_login", US_ora_login, usersub, filename);
- X make_usub("ora_open", US_ora_open, usersub, filename);
- X+ make_usub("ora_bind", US_ora_bind, usersub, filename);
- X make_usub("ora_fetch", US_ora_fetch, usersub, filename);
- X make_usub("ora_close", US_ora_close, usersub, filename);
- X make_usub("ora_logoff", US_ora_logoff, usersub, filename);
- X***************
- X*** 115,120 ****
- X--- 117,148 ----
- X }
- X /* NOTREACHED */
- X
- X+ case US_ora_bind:
- X+ if (items < 2)
- X+ fatal("Usage: &ora_bind($csr, $var ...)");
- X+ else {
- X+ char *csr = (char *) str_get(st[1]);
- X+ char **vars = (char **) malloc((items-1) * sizeof(char *));
- X+ int i, retval;
- X+
- X+ if (vars == NULL)
- X+ {
- X+ ora_errno = ORAP_NOMEM;
- X+ retval = 0;
- X+ }
- X+ else
- X+ {
- X+ for (i = 0 ; i < items - 1 ; i++)
- X+ {
- X+ vars[i] = (char *) str_get(st[i+2]);
- X+ }
- X+ retval = ora_bind(csr, vars, items - 1);
- X+ }
- X+
- X+ str_numset(st[0], (double) retval);
- X+ }
- X+ return sp;
- X+
- X CASE char * ora_close
- X I char * csr
- X END
- X***************
- X*** 202,207 ****
- X--- 230,243 ----
- X
- X case ORAP_NOSID:
- X str_set(str, "couldn't set ORACLE_SID");
- X+ break;
- X+
- X+ case ORAP_BADVAR:
- X+ str_set(str, "bad colon variable sequence");
- X+ break;
- X+
- X+ case ORAP_NUMVARS:
- X+ str_set(str, "wrong number of variables");
- X break;
- X
- X default:
- Xdiff -c /user/mis/kstock/tmp/orafns.c ./orafns.c
- X*** /user/mis/kstock/tmp/orafns.c Fri Aug 23 08:50:57 1991
- X--- ./orafns.c Mon Aug 5 11:01:40 1991
- X***************
- X*** 186,199 ****
- X }
- X
- X
- X! /* ora_open(lda, query)
- X *
- X! * sets and executes the specified sql query
- X */
- X
- X! char *ora_open(lda_s, query)
- X char *lda_s;
- X! char *query;
- X {
- X int i;
- X struct cursor *csr;
- X--- 186,199 ----
- X }
- X
- X
- X! /* ora_open(lda, stmt)
- X *
- X! * sets and executes the specified sql statement
- X */
- X
- X! char *ora_open(lda_s, stmt)
- X char *lda_s;
- X! char *stmt;
- X {
- X int i;
- X struct cursor *csr;
- X***************
- X*** 200,206 ****
- X struct cursor *lda = (struct cursor *) strtol(lda_s, (char **) NULL, 0);
- X short dbsize;
- X
- X! DEBUG(8, (fprintf(stderr, "ora_open(%#lx, %s)\n", (long) lda, query)));
- X
- X if (check_lda(lda) == 0)
- X {
- X--- 200,206 ----
- X struct cursor *lda = (struct cursor *) strtol(lda_s, (char **) NULL, 0);
- X short dbsize;
- X
- X! DEBUG(8, (fprintf(stderr, "ora_open(%#lx, %s)\n", (long) lda, stmt)));
- X
- X if (check_lda(lda) == 0)
- X {
- X***************
- X*** 216,224 ****
- X return((char *) NULL);
- X }
- X
- X if ((oopen(csr->csr, lda->csr, (char *)-1, -1, -1, (char *)-1, -1) != 0)
- X! || (osql3(csr->csr, query, -1) != 0)
- X! || (oexec(csr->csr) != 0))
- X {
- X ora_errno = csr->csr->csrrc;
- X ora_dropcursor(csr);
- X--- 216,238 ----
- X return((char *) NULL);
- X }
- X
- X+ /* Check whether there are any substitution variables in the statement
- X+ * If there are, we don't execute the statement yet.
- X+ */
- X+ if ((csr->varfields = count_colons(stmt)) < 0)
- X+ {
- X+ DEBUG(8, (fputs("ora_open: invalid variable sequence\n",
- X+ stderr)));
- X+ ora_errno = ORAP_BADVAR;
- X+ return((char *) NULL);
- X+ }
- X+
- X+ DEBUG(8, (fprintf(stderr,
- X+ "ora_open: statement contains %d colons\n", csr->varfields)));
- X+
- X if ((oopen(csr->csr, lda->csr, (char *)-1, -1, -1, (char *)-1, -1) != 0)
- X! || (osql3(csr->csr, stmt, -1) != 0)
- X! || ((csr->varfields == 0) && (oexec(csr->csr) != 0)))
- X {
- X ora_errno = csr->csr->csrrc;
- X ora_dropcursor(csr);
- X***************
- X*** 239,274 ****
- X
- X ora_errno = 0;
- X
- X! if ((csr->data = (char **) malloc(i * sizeof(char *))) == NULL)
- X! {
- X! DEBUG(128, (fputs("ora_open: out of memory\n", stderr)));
- X! DEBUG(8, (fputs("ora_open: returning NOMEM\n", stderr)));
- X! ora_errno = ORAP_NOMEM;
- X! ora_dropcursor(csr);
- X! return(0);
- X! }
- X! DEBUG(128, (fprintf(stderr, "ora_open: got data at %#lx\n",csr->data)));
- X! csr->nfields = i;
- X!
- X! for (i = 0 ; i < csr->nfields ; i++)
- X {
- X! odsc(csr->csr, i + 1, &dbsize, (short *) 0, (short *) 0,
- X! (short *) 0, (char *) 0, (short *) 0, (short *) 0);
- X!
- X! if ((csr->data[i] = (char *) malloc(dbsize + 1)) == NULL)
- X {
- X- csr->nfields = i;
- X- ora_dropcursor(csr);
- X-
- X DEBUG(128, (fputs("ora_open: out of memory\n",stderr)));
- X DEBUG(8, (fputs("ora_open: returning NOMEM\n",stderr)));
- X ora_errno = ORAP_NOMEM;
- X! return((char *) NULL);
- X }
- X! DEBUG(128, (fprintf(stderr, "ora_open: got field %d at %#lx\n",
- X! i, csr->data[i])));
- X! odefin(csr->csr, i + 1, csr->data[i], dbsize + 1, 5, 0,
- X! (short *) 0, (char *) 0, 0, 0, (short *) 0, (char *) 0);
- X }
- X
- X sprintf(address, "%#lx", (long) csr);
- X--- 253,300 ----
- X
- X ora_errno = 0;
- X
- X! if (i > 0)
- X {
- X! if ((csr->data = (char **) malloc(i * sizeof(char *))) == NULL)
- X {
- X DEBUG(128, (fputs("ora_open: out of memory\n",stderr)));
- X DEBUG(8, (fputs("ora_open: returning NOMEM\n",stderr)));
- X ora_errno = ORAP_NOMEM;
- X! ora_dropcursor(csr);
- X! return(0);
- X! }
- X! DEBUG(128, (fprintf(stderr,
- X! "ora_open: got data at %#lx\n",csr->data)));
- X! csr->nfields = i;
- X!
- X! for (i = 0 ; i < csr->nfields ; i++)
- X! {
- X! odsc(csr->csr, i + 1, &dbsize, (short *) 0, (short *) 0,
- X! (short *) 0, (char *) 0, (short *) 0, (short *) 0);
- X!
- X! if ((csr->data[i] = (char *) malloc(dbsize+1)) == NULL)
- X! {
- X! csr->nfields = i;
- X! ora_dropcursor(csr);
- X! DEBUG(128, (fputs("ora_open: out of memory\n",
- X! stderr)));
- X! DEBUG(8, (fputs("ora_open: returning NOMEM\n",
- X! stderr)));
- X! ora_errno = ORAP_NOMEM;
- X! return((char *) NULL);
- X! }
- X! DEBUG(128, (fprintf(stderr,
- X! "ora_open: got field %d at %#lx\n",
- X! i, csr->data[i])));
- X! odefin(csr->csr, i + 1, csr->data[i], dbsize + 1, 5, 0,
- X! (short *) 0, (char *) 0, 0, 0, (short *) 0,
- X! (char *) 0);
- X }
- X! }
- X! else
- X! {
- X! DEBUG(128, (fputs("ora_open: no data to return\n", stderr)));
- X! csr->data = NULL;
- X }
- X
- X sprintf(address, "%#lx", (long) csr);
- X***************
- X*** 309,314 ****
- X--- 335,397 ----
- X ora_errno = 0;
- X DEBUG(8, (fprintf(stderr,"ora_fetch: returning <%d>\n", csr->nfields)));
- X return(csr->nfields);
- X+ }
- X+
- X+
- X+ /* ora_bind(csr_s, vars, nitems)
- X+ *
- X+ * binds actual values to the SQL statement associated with csr
- X+ */
- X+
- X+ int ora_bind(csr_s, vars, nitems)
- X+ char *csr_s, **vars;
- X+ int nitems;
- X+ {
- X+ int i, ret;
- X+ struct cursor *csr = (struct cursor *) strtol(csr_s, (char **) NULL, 0);
- X+
- X+ DEBUG(8, (fprintf(stderr, "ora_bind(%#lx, %#lx, %d)\n",
- X+ (long) csr, (long) vars, nitems)));
- X+
- X+ if (check_csr(csr) == 0)
- X+ {
- X+ DEBUG(8, (fputs("ora_bind: returning 0\n", stderr)));
- X+ ora_errno = ORAP_INVCSR;
- X+ return(0);
- X+ }
- X+
- X+ if (csr->varfields != nitems)
- X+ {
- X+ DEBUG(8, (fprintf("ora_bind: expected %d items, got %d\n",
- X+ csr->varfields, nitems)));
- X+ ora_errno = ORAP_NUMVARS;
- X+ return(0);
- X+ }
- X+
- X+ for (i = 0 ; i < nitems ; i++)
- X+ {
- X+ if ((ret = obndrn(csr->csr, i+1, vars[i], strlen(vars[i])+1,
- X+ 5, -1, (short *) -1, (char *) -1, 0, 0)) != 0)
- X+ {
- X+ ora_errno = csr->csr->csrrc;
- X+ DEBUG(8, (fputs("ora_bind: returning 0\n", stderr)));
- X+ return(0);
- X+ }
- X+
- X+ DEBUG(8, (fprintf(stderr, "ora_bind: obndrv %d %s OK\n",
- X+ i + 1, vars[i])));
- X+ }
- X+
- X+ if (oexec(csr->csr) != 0)
- X+ {
- X+ ora_errno = csr->csr->csrrc;
- X+ DEBUG(8, (fputs("ora_bind: returning 0\n", stderr)));
- X+ return(0);
- X+ }
- X+
- X+ DEBUG(8, (fputs("ora_bind: oexec successful\n", stderr)));
- X+ DEBUG(8, (fputs("ora_bind: returning 1\n", stderr)));
- X+ return(1);
- X }
- X
- X
- Xdiff -c /user/mis/kstock/tmp/orafns.h ./orafns.h
- X*** /user/mis/kstock/tmp/orafns.h Fri Aug 23 08:50:57 1991
- X--- ./orafns.h Mon Aug 5 11:01:40 1991
- X***************
- X*** 17,23 ****
- X *ora_close(),
- X *ora_logoff();
- X
- X! int ora_fetch();
- X
- X
- X /* These functions are internal to the system, not for public consumption */
- X--- 17,24 ----
- X *ora_close(),
- X *ora_logoff();
- X
- X! int ora_bind(),
- X! ora_fetch();
- X
- X
- X /* These functions are internal to the system, not for public consumption */
- X***************
- X*** 66,72 ****
- X struct csrdef *csr;
- X char *hda, /* used if this cursor is an lda */
- X **data; /* used to receive database contents */
- X! int nfields; /* number of fields to retrieve */
- X struct cursor *next; /* list pointer */
- X };
- X
- X--- 67,74 ----
- X struct csrdef *csr;
- X char *hda, /* used if this cursor is an lda */
- X **data; /* used to receive database contents */
- X! int nfields, /* number of fields to retrieve */
- X! varfields; /* number of modifiable variables */
- X struct cursor *next; /* list pointer */
- X };
- X
- X***************
- X*** 73,78 ****
- X--- 75,81 ----
- X
- X /* functions that we use */
- X
- X+ int count_colons();
- X long strtol();
- X char *getenv(), *malloc();
- X
- X***************
- X*** 125,131 ****
- X
- X #define ORAP_ERRMIN 100000 /* lowest value allowed for an oraperl error */
- X
- X! #define ORAP_NOMEM 100001 /* out of memory */
- X! #define ORAP_INVCSR 100002 /* invalid cursor supplied */
- X! #define ORAP_INVLDA 100003 /* invalid lda supplied */
- X! #define ORAP_NOSID 100004 /* couldn't set ORACLE_SID */
- X--- 128,136 ----
- X
- X #define ORAP_ERRMIN 100000 /* lowest value allowed for an oraperl error */
- X
- X! #define ORAP_NOMEM 100001 /* out of memory */
- X! #define ORAP_INVCSR 100002 /* invalid cursor supplied */
- X! #define ORAP_INVLDA 100003 /* invalid lda supplied */
- X! #define ORAP_NOSID 100004 /* couldn't set ORACLE_SID */
- X! #define ORAP_BADVAR 100005 /* bad colon variable sequence */
- X! #define ORAP_NUMVARS 100006 /* wrong number of colon variables */
- Xdiff -c /user/mis/kstock/tmp/oraperl.1 ./oraperl.1
- X*** /user/mis/kstock/tmp/oraperl.1 Fri Aug 23 08:50:58 1991
- X--- ./oraperl.1 Mon Aug 5 11:01:41 1991
- X***************
- X*** 8,13 ****
- X--- 8,14 ----
- X .nf
- X $lda = &ora_login($database, $name, $password)
- X $csr = &ora_open($lda, $stmt)
- X+ &ora_bind($csr, $var, ...)
- X &ora_fetch($csr)
- X &ora_close($csr)
- X &ora_logoff($lda)
- X***************
- X*** 26,33 ****
- X using \fIora_login\fP.
- X This is called with three parameters,
- X the system ID of the \fIOracle\fP database to be used,
- X- (which \fIOracle\fP products expect
- X- in the \fBORACLE_SID\fP environment variable)
- X and the \fIOracle\fP username and password.
- X The return value is a login identifier
- X (an \fIORACLE Login Data Area\fP).
- X--- 27,32 ----
- X***************
- X*** 40,45 ****
- X--- 39,51 ----
- X The return value is a statement identifier
- X (an \fIORACLE cursor\fP).
- X
- X+ If the SQL statement contains substitution variables
- X+ (see later)
- X+ \fIora_bind\fP is used to assign values to them.
- X+ This takes a statement identifier (obtained from \fIora_open\fP)
- X+ as its first parameter,
- X+ followed by as many parameters as are required by the statement.
- X+
- X To retrieve the data returned from an \fISQL\fP \fBSELECT\fP statement,
- X the program should make successive calls to \fIora_fetch\fP.
- X This function takes a single parameter,
- X***************
- X*** 71,76 ****
- X--- 77,106 ----
- X from the last function call, and
- X \fIora_errstr\fP contains the \fIOracle\fP error message
- X corresponding to the current value of \fIora_errno\fP.
- X+ .SH SUBSTITUTION VARIABLES
- X+ \fIOraperl\fP allows an SQL statement to contain substitution variables.
- X+ These consist of a colon (\fB:\fP) followed by a number.
- X+ For example, a program which added records to a telephone list
- X+ might use the following call to \fIora_open\fP:
- X+
- X+ .ti +.5i
- X+ \f(CW$csr = &ora_open($csr, "insert into phonelist values(:1, :2)");\fP
- X+
- X+ The two names \fB:1\fP and \fB:2\fP are called substitution variables.
- X+ The function \fIora_bind\fP is used to assign values to these variables.
- X+ For example, the following statements would add two new people to the list:
- X+
- X+ .ti +.5i
- X+ \f(CW&ora_bind($csr, "Annette", "472-8836");\fP
- X+ .ti +.5i
- X+ \f(CW&ora_bind($csr, "Brian", "937-1823");\fP
- X+
- X+ Note that the substitution variables must be assigned consecutively
- X+ beginning from \fB1\fP for each SQL statement,
- X+ as \fBora_bind()\fP assigns its parameters in this order.
- X+ Named substitution variables
- X+ (for example, \fB:NAME\fP, \fB:TELNO\fP)
- X+ are not permitted.
- X .ne 28
- X .SH EXAMPLE
- X .if t .ft C
- X***************
- X*** 90,96 ****
- X
- X die ("You should use oraperl, not perl\n") unless defined &ora_login;
- X
- X! $lda = &ora_login("t", "name", "password")
- X || die $ora_errstr;
- X $csr = &ora_open($lda, "select * from telno order by name")
- X || die $ora_errstr;
- X--- 120,126 ----
- X
- X die ("You should use oraperl, not perl\n") unless defined &ora_login;
- X
- X! $lda = &ora_login("t", "kstock", "kstock")
- X || die $ora_errstr;
- X $csr = &ora_open($lda, "select * from telno order by name")
- X || die $ora_errstr;
- X***************
- X*** 151,157 ****
- X \fIORACLE\fP by Oracle Corporation, California.
- X .br
- X \fIPerl\fP by Larry Wall, Netlabs
- X! (\f(CWlwall@netlabs.com\fP, \f(CWlwall@netlabs.com\fP).
- X .br
- X \fIOraperl\fP by Kevin Stock, Encore Computer SA, France
- X (\f(CWkstock@gouldfr.encore.fr\fP).
- X--- 181,187 ----
- X \fIORACLE\fP by Oracle Corporation, California.
- X .br
- X \fIPerl\fP by Larry Wall, Netlabs
- X! (\f(CWlwall@netlabs.com\fP).
- X .br
- X \fIOraperl\fP by Kevin Stock, Encore Computer SA, France
- X (\f(CWkstock@gouldfr.encore.fr\fP).
- Xdiff -c /user/mis/kstock/tmp/oraperl.doc ./oraperl.doc
- X*** /user/mis/kstock/tmp/oraperl.doc Fri Aug 23 08:50:23 1991
- X--- ./oraperl.doc Mon Aug 5 11:01:41 1991
- X***************
- X*** 27,33 ****
- X because it requires fixed addresses to be specified for receipt of data.
- X A new interface was therefore created for \fBOraperl\fP.
- X
- X! The interface follows the idiom of the following five tasks:
- X
- X .in +5
- X .ta .4i 4.4i
- X--- 27,33 ----
- X because it requires fixed addresses to be specified for receipt of data.
- X A new interface was therefore created for \fBOraperl\fP.
- X
- X! The interface follows the idiom of the following six tasks:
- X
- X .in +5
- X .ta .4i 4.4i
- X***************
- X*** 34,51 ****
- X .nf
- X \fBTask Interface\fP
- X
- X! \fB1\fP log in to the database ora_login
- X! \fB2\fP open a stream for an SQL statement ora_open
- X! \fB3\fP get the data ora_fetch
- X! \fB4\fP close the stream ora_close
- X! \fB5\fP log off of the database ora_logoff
- X .fi
- X .in -5
- X
- X- Steps \fB2\fP and \fB3\fP are kept separate
- X- because a single query may produce a large amount of data.
- X
- X-
- X .ce 2
- X \fBCursors\fP
- X _______
- X--- 34,49 ----
- X .nf
- X \fBTask Interface\fP
- X
- X! \fB1\fP log in to the database \fIora_login\fP
- X! \fB2\fP open a stream for an SQL statement \fIora_open\fP
- X! \fB3\fP modify the statement \fIora_bind\fP
- X! \fB4\fP get the data \fIora_fetch\fP
- X! \fB5\fP close the stream \fIora_close\fP
- X! \fB6\fP log off of the database \fIora_logoff\fP
- X .fi
- X .in -5
- X
- X
- X .ce 2
- X \fBCursors\fP
- X _______
- X***************
- X*** 96,103 ****
- X
- X Requests a cursor (\fIcsr\fP)
- X and calls \fBOCI\ oopen\fP to connect it the the specified \fIlda\fP.
- X! It then calls \fBOCI\ osql3\fP to attach the SQL statement
- X! and \fBOCI\ oexec\fP to instruct \fIOracle\fP to execute it.
- X
- X If these three steps succeed,
- X \fBora_open\fP then makes successive calls to \fBOCI\ odsc\fP
- X--- 94,102 ----
- X
- X Requests a cursor (\fIcsr\fP)
- X and calls \fBOCI\ oopen\fP to connect it the the specified \fIlda\fP.
- X! It then calls \fBOCI\ osql3\fP to attach the SQL statement.
- X! If the statement does not contain any substitution variables,
- X! \fIora_open\fP calls \fBOCI\ oexec\fP to instruct \fIOracle\fP to execute it.
- X
- X If these three steps succeed,
- X \fBora_open\fP then makes successive calls to \fBOCI\ odsc\fP
- X***************
- X*** 105,110 ****
- X--- 104,118 ----
- X It allocates memory for these fields within \fIcsr\fP
- X and attaches them to the cursor using \fBOCI\ odefin\fP.
- X It returns the address of the \fIcsr\fP.
- X+
- X+
- X+ \fBora_bind(csr, var, ...)\fP
- X+
- X+ Binds the specified \fIvar\fPs to the substitution variables
- X+ in the SQL statement identified by \fIcsr\fP
- X+ and calls \fBOCI\ oexec\fP to execute the resulting statement.
- X+ Assumes that the substitution variables are \fB:1\fP, \fB:2\fP, \fB:3\fP, etc
- X+ in the order that they appear in the \fBora_bind\fP call.
- X
- X
- X \fBora_fetch(csr)\fP
- Xdiff -c /user/mis/kstock/tmp/oraperl.ref ./oraperl.ref
- X*** /user/mis/kstock/tmp/oraperl.ref Fri Aug 23 08:50:24 1991
- X--- ./oraperl.ref Mon Aug 5 11:01:41 1991
- X***************
- X*** 17,25 ****
- X Returns an \fIlda\fP for use with \fIora_open()\fP.
- X .sp
- X .ti -2m
- X! \fB$csr = &ora_login($lda, $statement)\fP
- X! Executes the given SQL statement in the database identified by $lda.
- X Returns a \fIcsr\fP for use with \fIora_fetch()\fP.
- X .sp
- X .ti -2m
- X \fB$n = &ora_fetch($csr)\fP
- X--- 17,31 ----
- X Returns an \fIlda\fP for use with \fIora_open()\fP.
- X .sp
- X .ti -2m
- X! \fB$csr = &ora_open($lda, $statement)\fP
- X! Associates the given SQL statement in the database identified by $lda.
- X! Executes it if it contains no substitution variables.
- X Returns a \fIcsr\fP for use with \fIora_fetch()\fP.
- X+ .sp
- X+ .ti -2m
- X+ \fB&ora_bind($csr, $var, ...)\fP
- X+ Binds the given values to the substition variables in the SQL statement
- X+ associated with $csr, and executes the resulting statement.
- X .sp
- X .ti -2m
- X \fB$n = &ora_fetch($csr)\fP
- SHAR_EOF
- chmod 0644 Patch02 ||
- echo 'restore of Patch02 failed'
- Wc_c="`wc -c < 'Patch02'`"
- test 23937 -eq "$Wc_c" ||
- echo 'Patch02: original size 23937, current size' "$Wc_c"
- fi
- # ============= colons.c ==============
- if test -f 'colons.c' -a X"$1" != X"-c"; then
- echo 'x - skipping colons.c (File already exists)'
- else
- echo 'x - extracting colons.c (Text)'
- sed 's/^X//' << 'SHAR_EOF' > 'colons.c' &&
- X/* colons.c
- X *
- X * Returns the number of substitution variables in an SQL query.
- X */
- X/* Copyright 1991 Kevin Stock.
- X *
- X * You may copy this under the terms of the GNU General Public License,
- X * or the Artistic License, copies of which should have accompanied your
- X * Perl kit.
- X */
- X
- Xint count_colons(s)
- Xregister char *s;
- X{
- X register int n = 0, c;
- X
- X while (*s != '\0')
- X {
- X if (*s == ':')
- X {
- X /* numbers must be used in sequence,
- X * but they may be repeated if a parameter is reused
- X */
- X if (((c = atoi(++s)) <= 0) || (c > n+1))
- X {
- X /* number too low or out of sequence */
- X return(-1);
- X }
- X else if (c == n + 1)
- X {
- X ++n;
- X }
- X /* else repeating a previous parameter */
- X }
- X else if (*s == '\'')
- X {
- X while ((*++s != '\'') && (*s != '\0'))
- X ;
- X }
- X if (*s != '\0')
- X {
- X ++s;
- X }
- X }
- X
- X return(n);
- X}
- SHAR_EOF
- chmod 0644 colons.c ||
- echo 'restore of colons.c failed'
- Wc_c="`wc -c < 'colons.c'`"
- test 830 -eq "$Wc_c" ||
- echo 'colons.c: original size 830, current size' "$Wc_c"
- fi
- # ============= mkdb.pl ==============
- if test -f 'mkdb.pl' -a X"$1" != X"-c"; then
- echo 'x - skipping mkdb.pl (File already exists)'
- else
- echo 'x - extracting mkdb.pl (Text)'
- sed 's/^X//' << 'SHAR_EOF' > 'mkdb.pl' &&
- X#!./oraperl
- X#
- X# mkdb.pl
- X#
- X# Sample oraperl program to create a new database and load data into it.
- X#
- X# Author: Kevin Stock
- X# Date: 5th August 1991
- X#
- X
- X# let's see what oraperl is doing when it executes this
- X
- X$ora_debug = 136;
- X
- X$CREATE = "create table tryit (name char(10), ext number(3))";
- X$INSERT = "insert into tryit values (:1, :2)";
- X$LIST = "select * from tryit order by name";
- X$DROP = "drop table tryit";
- X
- Xformat top =
- X Name Ext
- X ==== ===
- X.
- X
- Xformat STDOUT =
- X @<<<<<<<<< @>>
- X $name, $ext
- X.
- X
- Xdie ("You should use oraperl, not perl\n") unless defined &ora_login;
- X
- X# create the database
- X
- X$lda = &ora_login("t", "kstock", "kstock") || die $ora_errstr;
- X$csr = &ora_open($lda, $CREATE) || die $ora_errstr;
- Xdo ora_close($csr) || die $ora_errstr;
- X
- X# put some data into it
- X
- X$csr = &ora_open($lda, $INSERT) || die $ora_errstr;
- Xwhile (<DATA>)
- X{
- X m/([a-z]+):([0-9]+)/;
- X do ora_bind($csr, $1, $2);
- X}
- Xdo ora_close($csr) || die $ora_errstr;
- X
- X# list the result
- X
- X$csr = &ora_open($lda, $LIST) || die $ora_errstr;
- Xwhile (($name, $ext) = &ora_fetch($csr))
- X{
- X write;
- X}
- Xdo ora_close($csr) || die $ora_errstr;
- X
- X# remove the database
- X
- X$csr = &ora_open($lda, $DROP) || die $ora_errstr;
- Xdo ora_close($csr) || die $ora_errstr;
- Xdo ora_logoff($lda) || die $ora_errstr;
- X__END__
- Xdavid:225
- Xangela:208
- Xbruno:302
- Xalbert:294
- Xjulia:292
- Xalison:206
- Xarnold:305
- Xlarry:424
- Xcatherine:201
- Xrandall:306
- Xsusan:307
- SHAR_EOF
- chmod 0755 mkdb.pl ||
- echo 'restore of mkdb.pl failed'
- Wc_c="`wc -c < 'mkdb.pl'`"
- test 1444 -eq "$Wc_c" ||
- echo 'mkdb.pl: original size 1444, current size' "$Wc_c"
- fi
- exit 0
-
- exit 0 # Just in case...
-